# -*- coding: utf-8 -*-
import pymysql


class NativeSQL(object):
    def __init__(self, user, password, dbname, host='127.0.0.1', port=3306):
        self.user = user
        self.db = dbname
        self.password = password
        self.host = host
        self.port = port

    def query(self, sql, params={}):
        """
        :param sql:
        :param params:
        :return: data是一个list，每个元素是一个dict， key是列名,value是列值
        """
        code = 0
        msg = 'success'
        data = []
        try:
            connection = pymysql.connect(host=self.host,
                                         port=self.port,
                                         user=self.user,
                                         password=self.password,
                                         db=self.db)
            with connection.cursor() as cursor:
                cursor.execute(sql, params)
                columns = [col[0] for col in cursor.description]
                data = [dict(zip(columns, row)) for row in cursor.fetchall()]
        except Exception as e:
            code = -1
            msg = str(e)
        return code, msg, data

    def modify(self, sql, params={}):
        code = 0
        msg = 'success'
        try:
            connection = pymysql.connect(host=self.host,
                                         port=self.port,
                                         user=self.user,
                                         password=self.password,
                                         db=self.db)
            with connection.cursor() as cursor:
                cursor.execute(sql, params)
                connection.commit()
        except Exception as e:
            code = -1
            msg = str(e)
        return code, msg, []

    def executemany(self, sql, data):
        code = 0
        msg = 'success'
        try:
            connection = pymysql.connect(host=self.host,
                                         port=self.port,
                                         user=self.user,
                                         password=self.password,
                                         db=self.db)
            with connection.cursor() as cursor:
                cursor.executemany(sql, data)
                connection.commit()
        except Exception as e:
            connection.rollback()
            code = -1
            msg = str(e)
        return code, msg, []


if __name__ == "__main__":
    user = 'lucas'
    password = '5515612'
    dbname = 'flask_big_screen_db'
    sql = "select * from pie_data"
    obj = NativeSQL(user, password, dbname)
    code, msg, data = obj.query(sql)
    print(code, msg, data)
